package com.roc.service.impl;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.roc.bean.BlackListBean;
import com.roc.dao.DaoException;
import com.roc.dao.IBaseDao;
import com.roc.dao.ISQLBaseDao;
import com.roc.pojo.MerinfoStop;
import com.roc.pojo.Operators;
import com.roc.service.IStopService;
import com.roc.service.ServiceException;
import com.roc.util.Constance;
import com.roc.util.constant.SysLogType;

@Service
@Transactional
public class StopServiceImpl  implements IStopService {
	static Logger logger=Logger.getLogger(BlackServiceImpl.class);
	
	@Autowired
	private IBaseDao baseDao;
	@Autowired
	private ISQLBaseDao sqlBaseDao;
	/**
	 * @throws DaoException 
	 * 添加证件限制实现
	  * @Title: addBlackList
	  * @Description: TODO
	  * @param @param black
	  * @param @param op
	  * @param @return
	  * @param @throws ServiceException    
	  * @return int    
	  * @throws
	 */
	@Override
	public int  addStopList(MerinfoStop stop,Operators op) throws ServiceException {	
     
		try {
			stop.setDetail(stop.getDetail().trim());
			stop.setAgentId(op.getId());
			stop.setAgentName(op.getRealName());
			stop.setStatus(stop.getStatus().trim());
			stop.setMid(stop.getMid().trim());
			baseDao.editSave(stop);
			logger.info("添加【"+stop.getMid()+"】到商户限制成功!");
			baseDao.addSyslog(SysLogType.BLACK_ADD.getCode(), SysLogType.BLACK_ADD.getName(), op.getAccountNo(), op.getAccountNo()+"添加【"+stop.getMid()+"】到黑名单");
		} catch (DaoException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			logger.debug(e.getMessage(),e);
			throw new ServiceException("添加商户限制失败");
		}
		 return 1;  
  	   }
	/**
	 * 删除实现
	 */
	@Override
	public int deleteStopNum(String blackNum) throws ServiceException {
		String sql=" delete  FROM T_MERINFO_STOP WHERE mid=?  ";
		int effect=0;
		List<Object> parmas=new ArrayList<Object>();
		parmas.add(blackNum);				
		try {
			effect=sqlBaseDao.deleteBySql(sql, parmas);
			if(effect>0)
			{
				logger.info("删除黑名单【"+blackNum+"】成功!");		
			}
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("更改商户限制失败");
		}
		return effect;
	}
	@Override
	public MerinfoStop findByMid(String blackNum) throws ServiceException {	
			String sql=" SELECT DETAIL,AGENT_ID,AGENT_NAME,CREATETIME,STATUS,MID FROM T_MERINFO_STOP  where mid=? ";
			Object[] parmas={blackNum};
			MerinfoStop stop=null;
			try {
				Object object=sqlBaseDao.findObj(sql, parmas);
				if(object!=null)
				{
					Object[] obj=(Object[]) object;
					stop=new MerinfoStop();
					
					stop.setDetail(Constance.Null2EmptyTrim(obj[0]));
					stop.setAgentId(Long.parseLong(Constance.Null2EmptyTrim(obj[1])));
					stop.setAgentName(Constance.Null2EmptyTrim(obj[2]));
					stop.setCreatetime(Constance.Null2EmptyTrim(obj[3]));
					stop.setStatus(Constance.Null2EmptyTrim(obj[4]));
					stop.setMid(Constance.Null2EmptyTrim(obj[5]));
				}
			} catch (DaoException e) {
				logger.debug(e.getMessage(),e);
				throw new ServiceException("查询【"+blackNum+"】失败");
			}
			return stop;
		}
	
	/**
	 * 进件限制详情
	 */
	@Override
	public List<Object> limitiDetilQuery(String mid, String status) throws ServiceException {
		StringBuffer sql=new StringBuffer();
		sql.append("select mi.mcht_cd,  \n ") ;       //商户号
		sql.append("       mi.name,   \n ") ;       //商户名
		sql.append("       (select i.ins_name   \n ") ;
		sql.append("          from Tbl_Ins_Inf i  \n ") ;
		sql.append("         where i.ins_company_cd = mi.aip_bran_cd and Ins_Type='1' and rownum=1 ) ins_name,  \n ") ;   //所属平台
		sql.append("       nvl((select o1.real_name from t_operators o1 where o1.id = (select o.oneagentid  from t_operators o where o.id = mi.ext1)),(select o.real_name  from t_operators o where o.id = mi.ext1)) oneagentname,   \n ") ;  //一级代理商
		sql.append("       nvl((select o1.real_name from t_operators o1 where o1.id = (select o.companyid  from t_operators o where o.id = mi.ext1)),(select o.real_name  from t_operators o where o.id = mi.ext1)) companyname,? mid  \n ") ;  //所属分公司
		sql.append("  from tbl_mcht_inf mi   \n ") ;
		sql.append(" where 1=1    \n ") ;
		if(status.equals("1")){
		  sql.append("   and mi.busi_lice_no = ?   \n ") ;       //营业执照号码 
		}else if(status.equals("2")){
		  sql.append("   and exists (select 1 from TBL_MCHT_BANKACCOUNT mb where mb.owner_cd=mi.mcht_cd and mb.account=?)  \n ") ;  //结算卡号
		}else if(status.equals("0")){
		  sql.append("   and mi.certif_no = ?  \n ") ;       //法人证件号码
		}else if(status.equals("3")){
		  sql.append("   and mi.Stlm_Way_Desc = ? \n ") ;       //非法人身份证
		}else if(status.equals("4")){
		sql.append("   and mi.CONTACT_PHONENO = ? \n ") ;       //手机号
	    }
		Object[] parmas={mid,mid};
		List<Object> list=new ArrayList<Object>();
		try {
			list=sqlBaseDao.queryAll(sql.toString(), parmas);
		
		} catch (DaoException e) {
			logger.debug(e.getMessage(),e);
			throw new ServiceException("查询【"+mid+"】失败");
		}
		return list;
	}
	
	
	
	/**
	 * 批量导入进件限制
	 */
	@Override
	public Map<String, Object> insertExportBlackList(File file,Operators op) throws ServiceException {
		Map<Integer,List<Object>> uploadData=new HashMap<Integer, List<Object>>();//存放上传数据
		Map<String,Object> resultMap=new HashMap<String, Object>();//上传文件错误原因及定位
		int count=0;
		try{		
		//上传Excel文件流转换为Map集合
		logger.info("上传Excel文件流转换为Map集合");
		boolean uploadResult=transfromToExcel(file, uploadData,resultMap);
		if(!uploadResult)
		{
			return resultMap;//返回错误原因
		}
		//验证Excel表数据正确性
		logger.info("验证Excel表数据正确性");
		boolean correntValidate=verifyDataCorrectness(uploadData, resultMap);
		if(!correntValidate)
		{
			return resultMap;//返回校验原因
		}
		//导入数据库
		logger.info("开始进行批量导入操作");
		/*SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
		String date=sdf.format(new Date());*/
		Iterator<Entry<Integer, List<Object>>> it=uploadData.entrySet().iterator();
		//检测是否重复数据
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();
			//循环列					
			String blackNum=Constance.Null2EmptyTrim(list.get(0));			
			MerinfoStop temp=findByMid(blackNum);
			if(temp!=null)
			{
				resultMap.put("result","导入失败,证件号:【"+blackNum+"】已存在!!!");
				logger.info("停止批量导入,已存在数据【"+blackNum+"】");
				return resultMap;
			}
		}
		//开始导入数据
		it=uploadData.entrySet().iterator();
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();
			//循环列					
			MerinfoStop stop=new MerinfoStop();
			stop.setMid(Constance.Null2EmptyTrim(list.get(0)));
			stop.setStatus(Constance.Null2EmptyTrim(list.get(1)));
			stop.setDetail(Constance.Null2EmptyTrim(list.get(2)));
			stop.setAgentId(op.getId());
			stop.setAgentName(op.getRealName());
			stop.setCreatetime(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
			baseDao.editSave(stop);
			count++;
		}
		}catch(Exception e){
			logger.debug(e.getMessage(), e);
			throw new ServiceException("批量导进件限制失败");
		}
		resultMap.put("result","导入成功!总计导入"+count+"条数据");
		return resultMap;
	}
	
	
	
	/**
	 * 验证数据正确性
	 * @param map
	 */
	public boolean verifyDataCorrectness(Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		Iterator<Entry<Integer, List<Object>>> it=map.entrySet().iterator();
		String error_reason=null;
		while(it.hasNext())
		{
			Entry<Integer, List<Object>> entry=it.next();
			List<Object> list=entry.getValue();			
			//商户号或卡号验证
			String regex="^[a-zA-Z0-9']{1,30}$";
			String num=Constance.Null2EmptyTrim(list.get(0));
			if(!Pattern.matches(regex,num))
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号或卡号不规范,只能包含数字字符且长度不能超过30，中间也不能有空格并且不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			//String num=Constance.Null2EmptyTrim(list.get(0));
			if("".equals(num)||num==null)
			{
				error_reason="第"+(entry.getKey()+1)+"行第1列商户号或卡号不规范,也不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			
			String cardType=Constance.Null2EmptyTrim(list.get(1));
			if(!cardType.equals("0")&&!cardType.equals("1")&&!cardType.equals("2")&&!cardType.equals("3")&&!cardType.equals("4")&&!cardType.equals("O"))
			{
				error_reason="第"+(entry.getKey()+1)+"行第2列卡类型值不规范,只能为0,1,2,3,4中的一个;";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
			String reason=Constance.Null2EmptyTrim(list.get(2));
			if(reason.length()>100||"".equals(reason))
			{
				error_reason="第"+(entry.getKey()+1)+"行第3列原因值不规范,长度不能超过100且不能为空";
				logger.info(error_reason);
				resultMap.put("result", error_reason);
				return false;
			}
		}
		return true;
	}
	/**
	 * 将File装换成Map集合
	 * @param file
	 * @param map
	 */
	@SuppressWarnings("deprecation")
	protected boolean transfromToExcel(File file,Map<Integer,List<Object>> map,Map<String,Object> resultMap)
	{
		try {
			// 构造 XSSFWorkbook 对象，strPath 传入文件路径
			XSSFWorkbook xwb = new XSSFWorkbook(file.getPath());
			// 得到Excel工作表对象
			XSSFSheet sheet = xwb.getSheetAt(0);
			// 得到行数
			int trLength = sheet.getPhysicalNumberOfRows();
			if(trLength == 0){
				logger.info("上传文件缺少列名");
				resultMap.put("result", "上传文件缺少列名");
				return false;
			}			
			logger.info("Excel中记录的总行数是：" + (trLength - 1));			
			// 每条记录列数
			Row row = sheet.getRow(0);
						
			int tdLength = row.getPhysicalNumberOfCells();
			if(tdLength<3){
				logger.info("上传的文件缺少必需的列名");
				resultMap.put("result", "上传的文件缺少必需的列名");
				return false;
			}
			if(tdLength>3){
				logger.info("上传的文件存在多余的列名");
				resultMap.put("result", "上传的文件存在多余的列名");
				return false;
			}
			logger.info("每条记录的列数："+tdLength);
				
			//验证标题是否一致
			String reason_error=null;
			String[] title={"证件号","类型","原因"};
			String titleName=null;
			for(int i=0;i<3;i++)
			{
				row.getCell(i).setCellType(Cell.CELL_TYPE_STRING);
				titleName=row.getCell(i).getStringCellValue();
				if(!titleName.equals(title[i]))
				{
					reason_error="第1行第"+(i+1)+"列的列名不正确,正确的列名为【"+title[i]+"】";
					logger.info(reason_error);
					resultMap.put("result", reason_error);
					return false;
				}
			}
			if(trLength == 1){
				logger.info("上传文件内容为空");
				resultMap.put("result", "上传文件内容为空");
				return false;
			}
			for (int i = 1; i < trLength; i++) {
				List<Object> tdList = new ArrayList<Object>();
				Row r = sheet.getRow(i);
				
				for (int j = 0; j < tdLength; j++) {
					Cell c = r.getCell(j);
					/*
					 * 将所有列的内容都设置成String类型，否则会出Excel异常： Cannot get a text value
					 * from a numeric cell
					 */
					if (c != null) {
						c.setCellType(Cell.CELL_TYPE_STRING);
						tdList.add(c.getStringCellValue());
					}else{
						tdList.add("");
					}					
				}
				map.put(i, tdList);
			}
		} catch (Exception e) {
			logger.error("将Excel中数据封装到List容器中异常：" + e.getMessage());
			resultMap.put("result", "将Excel中数据封装到List容器中异常");
			return false;
		}
		return true;
	}
		
	}
	


